package com.example.demo01.Impl.mobileImpl;

import com.example.demo01.Dao.mobileDao.ServePointDao;
import com.example.demo01.Entity.mobileEntity.ServePoint;
import com.example.demo01.Entity.mobileEntity.ServicePointEntity;
import com.example.demo01.utils.JDBCPGUtils;
import org.junit.jupiter.api.Test;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class ServePointDaoImpl implements ServePointDao {

    /**
     * 获取超市点集合
     */
    @Override
    public List<ServePoint> getSuperMarketList(String lng, String lat) {
        String sql = "select\n" +
                "\tname,\n" +
                "\taddress,\n" +
                "\tprovince,\n" +
                "\tcity,\n" +
                "\tdomain,\n" +
                "\tlon,\n" +
                "\tlat,\n" +
                "\tgeom,\n" +
                "\t'购物点' as type,\n" +
                "\tst_distance3857(\n" +
                "\t\tgeom,\n" +
                "\t\tst_geomfromtext('POINT(" + lng + " " + lat + ")',4326)\n" +
                "\t)  as distance\n" +
                "from supermarketpoints order by distance asc";
        return getServePointListBySQL(sql);
    }

    /**
     * 获取医疗点集合
     */
    @Override
    public List<ServePoint> getHospitalList(String lng, String lat) {
        String sql = "select\n" +
                "\tname,\n" +
                "\taddress,\n" +
                "\tprovince,\n" +
                "\tcity,\n" +
                "\tdomain,\n" +
                "\tlng as lon,\n" +
                "\tlat,\n" +
                "\tgeom,\n" +
                "\t'医疗点' as type,\n" +
                "\tst_distance3857(\n" +
                "\t\tgeom,\n" +
                "\t\tst_geomfromtext('POINT(" + lng + " " + lat + ")',4326)\n" +
                "\t)  as distance\n" +
                "from hospital order by distance asc";
        return getServePointListBySQL(sql);
    }

    /**
     * 获取休闲点集合
     */
    @Override
    public List<ServePoint> getSceneryPointList(String lng, String lat) {
        String sql = "select\n" +
                "\tname,\n" +
                "\taddress,\n" +
                "\tprovince,\n" +
                "\tcity,\n" +
                "\tdomain,\n" +
                "\tlon,\n" +
                "\tlat,\n" +
                "\tgeom,\n" +
                "\t'休闲点' as type,\n" +
                "\tst_distance3857(\n" +
                "\t\tgeom,\n" +
                "\t\tst_geomfromtext('POINT(" + lng + " " + lat + ")',4326)\n" +
                "\t)  as distance\n" +
                "from scenerypoints order by distance asc";
        return getServePointListBySQL(sql);
    }

    /**
     * 获取(lng,lat)周围3km范围内的服务点
     * @param lng 经度
     * @param lat 纬度
     * @return (lng, lat)周围3km范围内的服务点列表
     */
    @Override
    public List<ServicePointEntity> getServePointByBuffer(String lng, String lat) {
        //声明结果集
        List<ServicePointEntity> pointEntityList=new ArrayList<>();
        //1-获取当前点的缓冲区geometry
        //2-根据缓冲区geometry计算周围3km范围内的服务点列表
       //声明变量
        //超市
        String sqlSuperMarket="select \n" +
                "\tname,lon,lat\n" +
                "from supermarketpoints\n" +
                "where\n" +
                "\tst_within(\n" +
                "\t\tst_transform(ST_SetSRID(st_geomfromtext('POINT('||lon||' '||lat||')'),4326),3857),\n" +
                "\t\tST_Buffer(st_transform(ST_SetSRID(ST_GeomFromText('POINT("+lng+" "+lat+")'),4326),3857),5000,'quad_segs=8')\n" +
                "\t)='t'";
        //休闲点
        String sqlGardens="select \n" +
                "\tname,lon,lat\n" +
                "from supermarketpoints\n" +
                "where\n" +
                "\tst_within(\n" +
                "\t\tst_transform(ST_SetSRID(st_geomfromtext('POINT('||lon||' '||lat||')'),4326),3857),\n" +
                "\t\tST_Buffer(st_transform(ST_SetSRID(ST_GeomFromText('POINT("+lng+" "+lat+")'),4326),3857),5000,'quad_segs=8')\n" +
                "\t)='t'";
        //医院
        String sqlHospitals="select name,lng as lon,lat from hospital\n" +
                "where\n" +
                "\tst_within(\n" +
                "\t\tst_transform(ST_SetSRID(st_geomfromtext('POINT('||lng||' '||lat||')'),4326),3857),\n" +
                "\t\tST_Buffer(st_transform(ST_SetSRID(ST_GeomFromText('POINT("+lng+" "+lat+")'),4326),3857),5000,'quad_segs=8')\n" +
                "\t)='t'";
        //获取结果集
        List<ServicePointEntity> list = getAimedList(sqlSuperMarket);//超市解析结果
        List<ServicePointEntity> list1=getAimedList(sqlGardens);//休闲点解析结果
        List<ServicePointEntity> list2=getAimedList(sqlHospitals);//医院解析结果
        //获取最终结果
        pointEntityList.addAll(list);
        pointEntityList.addAll(list1);
        pointEntityList.addAll(list2);
        return pointEntityList;
    }


    public List<ServicePointEntity> getAimedList(String sql){
        List<ServicePointEntity> list=new ArrayList<>();
        ServicePointEntity servicePointEntity=null; //临时对象
        //声明变量
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        //数据库操作
        try {
            connection=JDBCPGUtils.getConnection();
            statement=connection.prepareStatement(sql);
            resultSet=statement.executeQuery();
            //解析查询结果
            while (resultSet.next()){
                servicePointEntity=new ServicePointEntity(
                        resultSet.getString("name"),
                        Float.parseFloat(resultSet.getString("lon")),
                        Float.parseFloat(resultSet.getString("lat"))
                );
                list.add(servicePointEntity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCPGUtils.close(resultSet,statement,connection);
        }
        return list;
    }


    /**
     * 根据坐标值与经纬度获取缓冲区要素geometry
     *
     * @param lng
     * @param lat
     * @param distance
     * @return
     */
    public String getBufferWithDis(String lng, String lat, String distance) {
        String resGeometry = "";//记录结果
        String sqlQueryBufferGeom = "select ST_Buffer(st_transform(ST_SetSRID(ST_GeomFromText('POINT("
                + lng + ' ' + lat +
                ")'),4326),3857)," + distance +
                ",'quad_segs=8') as geom";
        System.out.println(sqlQueryBufferGeom);
        //声明变量
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //数据库操作
        try {
            connection = JDBCPGUtils.getConnection();
            statement = connection.prepareStatement(sqlQueryBufferGeom);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                resGeometry = resultSet.getString("geom");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCPGUtils.close(resultSet, statement, connection);
        }
        return resGeometry;
    }

    @Test
    public void getGeom() {
//        System.out.println(getBufferWithDis("113.159774130917", "35.06731502773802", "3000"));
        System.out.println(getServePointByBuffer("113.159774130917", "35.06731502773802"));
    }

    /**
     * 根据SQL语句获取结果集
     *
     * @param sql SQL语句
     * @return 结果集列表List<ServePoint>
     */
    public List<ServePoint> getServePointListBySQL(String sql) {
        List<ServePoint> list = new ArrayList<>();
        ServePoint servePoint = null;
        //声明变量
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //数据库操作，异常处理
        try {
            connection = JDBCPGUtils.getConnection();
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                servePoint = new ServePoint(
                        resultSet.getString("name"),
                        resultSet.getString("address"),
                        resultSet.getString("province"),
                        resultSet.getString("city"),
                        resultSet.getString("domain"),
                        resultSet.getFloat("lon"),
                        resultSet.getFloat("lat"),
                        resultSet.getString("geom"),
                        resultSet.getString("type"),
                        resultSet.getFloat("distance")
                );
                list.add(servePoint);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCPGUtils.close(resultSet, statement, connection);
        }
        return list;
    }

}
